/************************************************************************
Author: Matthew Williamson (matthew.williamson@murraystate.edu)
Date: 4/4/2011
Description: Installs the sample scripts for the VOEIS Hub Push Service.
Prerequisites: 
	1.  The VOEIS Hub Push Service must be installed and configured on your server.
	2.  You must have the LittleBear11 database installed on the 
		database server that you are going to run the service and samples from.
		The LittleBear11 database can be downloaded from CUAHSI at 
		http://his.cuahsi.org/software/LittleBear1.1TestingDatabase.zip
	3.  You must set up a test project at the VOEIS hub
			voeis.msu.montana.edu
		a.  You must know your api key a site to upload the data to.
		b.  You must set up a data stream and know the template ID.  Base
			your data streams on the views created by this script.
	4.  Once the samples are set up, you must update the VOEISHubSync table entries
		with the data gathered in the preceeding step.  This can be done by either
		issuing update statements, or using the stored procedure 'dbo.UpdateSamplesMetadata'
		and passing in the appropriate parameters.
	5.  Start your service.
****************************************************************************/


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
	
-- CHECK FOR THE LITTLEBEAR11 DATABASE
IF EXISTS (SELECT * FROM sys.sysdatabases where name='LittleBear11')
BEGIN
	-- CHECK FOR THE VOEISPushService SERVER LOGIN
	IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'VOEISPushService')
	BEGIN
		GOTO ServerPrincipal_Missing
	END
	
	-- CHECK FOR THE VOEISPushService DATABASE LOGIN
	IF NOT EXISTS (SELECT name FROM LittleBear11.sys.database_principals WHERE name = 'VOEISPushService')
	BEGIN
		USE LittleBear11;
		
		DECLARE @SQL NVARCHAR(MAX)
		SET @SQL = N''
		
		SET @SQL = N'CREATE USER [VOEISPushService] FOR LOGIN [VOEISPushService] WITH DEFAULT_SCHEMA=[db_datareader]'
		
		EXEC sp_executesql @SQL
	END
	
	-- SET ROLE PERMISSIONS FOR VOEISPushService on LBR11
	USE LittleBear11;
	EXEC [sp_addrolemember]		@rolename = 'db_datareader',		@membername = 'VOEISPushService'
END
ELSE
BEGIN
	GOTO LBR11_Missing
END


-- CHECK FOR THE VOEISPushService DATABASE
IF EXISTS (SELECT * FROM sys.sysdatabases where name='VOEISPushService')
BEGIN

	-- CREATE THE VIEWS
	USE VOEISPushService;
	
	SET @SQL = N''
	SET @SQL = N'CREATE VIEW [dbo].[uvw_VOEISPush_LBR_Paradise]
	AS
	SELECT     TOP (100) PERCENT T1.LocalDateTime, T1.DataValue AS [Gage Height], T2.DataValue AS Discharge
	FROM         LittleBear11.dbo.DataValues AS T1 LEFT OUTER JOIN
							  (SELECT     LocalDateTime, DataValue
								FROM          LittleBear11.dbo.DataValues
								WHERE      (SiteID = 12) AND (VariableID = 43)) AS T2 ON T1.LocalDateTime = T2.LocalDateTime
	WHERE     (T1.SiteID = 12) 
			AND (T1.VariableID = 42)
			AND (T1.LocalDateTime > 
					(SELECT LastRecordDateTime 
						FROM VOEISPushService.dbo.VOEISHubSync
						WHERE ParentTable = ''uvw_VOEISPush_LBR_Paradise''))'
	EXEC sp_executesql @SQL
	
	SET @SQL = N''
	SET @SQL = N'CREATE VIEW [dbo].[uvw_VOEISPush_LBR_ParadiseRepeater]
	AS
	SELECT     TOP (100) PERCENT LocalDateTime, DataValue AS [Battery Voltage]
	FROM         LittleBear11.dbo.DataValues
	WHERE     (SiteID = 8) 
			AND (VariableID = 23)
			AND (LocalDateTime > 
					(SELECT LastRecordDateTime 
						FROM VOEISPushService.dbo.VOEISHubSync
						WHERE ParentTable = ''uvw_VOEISPush_LBR_ParadiseRepeater''))'
	EXEC sp_executesql @SQL
	
	SET @SQL = N''
	SET @SQL = N'CREATE PROCEDURE [dbo].[UpdateSamplesMetadata]
	@ProjectID NVARCHAR(100),
	@DataTemplateID INTEGER,
	@Enabled BIT,
	@SyncID BIGINT 
	AS
	BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;

		-- Insert statements for procedure here
		DECLARE @SQL NVARCHAR(MAX), @PARAMS NVARCHAR(MAX)
		SET @SQL = N''UPDATE dbo.VOEISHubSync SET ProjectID = '' + QUOTENAME(@ProjectID, '''''''') + '', DataTemplateID = '' + CAST(@DataTemplateID AS NVARCHAR(10))
						+ '', [Enabled] = '' + CAST(@Enabled AS NVARCHAR(1)) + '' WHERE SyncID = '' + CAST(@SyncID AS NVARCHAR(10))
		SET @PARAMS = N''@ProjectID NVARCHAR(100),
						@DataTemplateID INTEGER,
						@Enabled BIT,
						@SyncID BIGINT''
		EXEC sp_executesql @SQL, @PARAMS, @ProjectID, @DataTemplateID, @Enabled, @SyncID
		
		SELECT @@ROWCOUNT
	END'
	
	EXEC sp_executesql @SQL

	-- INSERT METADATA
	INSERT INTO VOEISPushService.dbo.VOEISHubSync(DataSet, ParentTable, DefaultFileName, ProjectID,
		DataTemplateID, LastRecordDateTime, LastResult, RecordingDate_CT, RecordingDate_UTC,
		[Enabled])
		VALUES('LBR at Paradise, Utah', 'uvw_VOEISPush_LBR_Paradise', 'LBR_Paradise.csv', 'your-project-id', 0, '1/1/1900 00:00:00', NULL, NULL,	NULL, 0)


	INSERT INTO VOEISPushService.dbo.VOEISHubSync(DataSet, ParentTable, DefaultFileName, ProjectID,
		DataTemplateID, LastRecordDateTime, LastResult, RecordingDate_CT, RecordingDate_UTC,
		[Enabled])
		VALUES('LBR radio repeater at Paradise, Utah', 'uvw_VOEISPush_LBR_ParadiseRepeater', 'LBR_ParadiseRepeater.csv', 'your-project-id', 0, '1/1/1900 00:00:00', NULL, NULL,	NULL, 0)

	GOTO STOP_SCRIPT;
END
ELSE
BEGIN
	GOTO VOEISPushService_Missing
END


LBR11_Missing:
	BEGIN
		PRINT N'The LittleBear11 database is missing.  Please visit http://his.cuahsi.org/software/LittleBear1.1TestingDatabase.zip to download and install the database before running this script again.'
		GOTO STOP_SCRIPT
	END
	
ServerPrincipal_Missing:
	BEGIN
		PRINT N'The VOEISPushService server priincipal is missing.  Please ensure that you have installed the VOEIS Push Service on this machine and the VOEISPushService user exists before running this script again.'
		GOTO STOP_SCRIPT
	END
	
VOEISPushService_Missing:
	BEGIN
		PRINT N'The VOEISPushService database is missing.  Please download and install the service before running this script again.'
		GOTO STOP_SCRIPT
	END
	
STOP_SCRIPT:
	BEGIN
		PRINT N'Script has been terminated'
	END
-- END OF SCRIPT